Release 10.1A: OpenEdge Reporting:
Query/Results for UNIX

Determining files and selection criteria

Use the following shared variables in your Progress procedures to determine the files and selection criteria defined when the user selects the User module:

DEFINE SHARED VARIABLE qbf-db AS CHARACTER EXTENT 5 NO-UNDO. DEFINE SHARED VARIABLE qbf-file AS CHARACTER EXTENT 5 NO-UNDO. DEFINE SHARED VARIABLE qbf-where AS CHARACTER EXTENT 5 NO-UNDO. DEFINE SHARED VARIABLE qbf-of AS CHARACTER EXTENT 5 NO-UNDO.

When using these shared variables:

Results currently supports up to five joins between files in all modules.
Results sets each unused array entry to contain an empty string (“”) and places it at the end of the array so that no gaps appear in the qbf-file and qbf-db arrays. For example, qbf-file[1] = “customer” qbf-file[2] = “” qbf-file[3] = “order” cannot process because there is an unused entry (shown as "") between array entries one and three. Instead, the array contains qbf-file[1] = “customer” qbf-file[2] = “order”, where the unused entry appears at the end of the array.
The WHERE clauses contained in the qbf-where variable can be blank or contain the WHERE expression. The value contained within each array element does not begin with the keyword “WHERE.” If the qbf-where element is blank, it means that there is no WHERE criteria for the corresponding file. (However, there might be a corresponding relation in qbf-of.)

The qbf-where [ ] variable might also contain the questions for Ask mode. (These are the prompts users see when they run a WHERE clause that uses Ask mode. They prompt users for a value at runtime.) If INDEX(qbf-where [ ],"~{~{") is greater than 0, then you must do additional processing before you can use the WHERE clause.

For example, Results stores the run-time ask information in the WHERE clause between double sets of curly braces {{ and }}. To use the WHERE clause, everything from {{ to }} inclusive must be replaced with a literal value.

Between these braces, Results stores several fields: the data type (followed by a comma), the field name (followed by a comma), a relational operator (followed by a colon), and the user question. For example, qbf-where [1 ] for the customer file might contain this code:

sports.customer.St = {{character,sports.customer.St, Equal:Enter state for report}}

This means, “Extract all records where the field sports.customer.St is equal to a user-supplied character value, and prompt the user with the question Enter state for report. Then place the user-supplied value into qbf-where [ ] in the place of character...report.

The OF clause stores the relation. It begins with OF or WHERE, except for qbf-of [1], which is usually empty. You can use the following code to combine the qbf-of and qbf-where variables:

DEFINE VARIABLE c AS CHARACTER NO-UNDO. DEFINE VARIABLE i AS INTEGER NO-UNDO. PUT UNFORMATTED ’FOR ’. DO i = 1 TO 5 WHILE qbf-file[qbf-i] <> "": c = (IF qbf-where[i] = "" THEN qbf-of[i] ELSE IF qbf-of[i] = "" THEN "WHERE " + qbf-where[i] ELSE IF qbf-of[i] BEGINS "OF" THEN qbf-of[i] + " WHERE " + qbf-where[i] ELSE qbf-of[i] + " AND " + qbf-where[i] ). IF c <> "" THEN c = " " + c. PUT UNFORMATTED ’EACH ’ qbf-db[i] ’.’ qbf-file[i] c ’ NO-LOCK’. IF i < 5 AND qbf-file[i + 1] <> "" THEN PUT UNFORMATTED ’,’ SKIP ’ ’. ELSE PUT UNFORMATTED ’:’ SKIP. END.

Note: Code similar to this is used in the u-export.p sample export format program in the "Designing your own export format" section.

Use this program listing in conjunction with the qbf-file, qbf-where, and qbf-of shared variables to produce a Progress 4GL selection statement similar to the following:

FOR EACH sports.customer, EACH sports.order OF sports.customer, EACH sports.order-line WHERE sports.order.order_num =sports.order-line.order-num . . .

This program is important because you use these entry points to determine the files and selection criteria already defined when the User module was selected. Note that when there is a join operation, Results shows all parent relationships, not just the immediate parents.